package com.orange.modules.dao.expert;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.dbhelp.page.Page;
import com.orange.modules.entity.expert.AuthExpertAdvice;

@Repository
public class AuthExpertAdviceRepository {

	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthExpertAdviceDao authExpertAdviceDao;
	
	/**
	 * 保存专家建议
	 */
	public void saveAuthExpertAdvice(AuthExpertAdvice a){
		authExpertAdviceDao.save(a);
	}
	
	/**
	 * 查询专家建议(根据专家id和组id)
	 * @throws SQLException 
	 */
	public List<AuthExpertAdvice> queryAuthExpertAdviceByExpertIdAndGroupId(String expertId, String groupId) throws SQLException{
		String sql = "Select a.p_id as pid, a.s_expert_id as expertId, s_group_id as groupId, a.s_keyword as keyword,s_detailed_advice as detailedAdvice, a.s_create_time as createTime " +
				" from t_auth_expert_advice as a where a.s_expert_id = ? and a.s_group_id = ?";
		return dbHelper.getBeanList(sql, AuthExpertAdvice.class, expertId,groupId);
	}
	
	/**
	 *  查询专家建议(根据pid)
	 */
	public List<AuthExpertAdvice> queryAuthExpertAdviceById(String pid) throws SQLException{
		String sql = "Select a.p_id as pid, a.s_expert_id as expertId, s_group_id as groupId, a.s_keyword as keyword,s_detailed_advice as detailedAdvice, a.s_create_time as createTime " +
				" from t_auth_expert_advice as a where a.p_id = ?";
		return dbHelper.getBeanList(sql, AuthExpertAdvice.class, pid);
	}
	
	/**
	 * 分页查询专家建议
	 * @throws SQLException 
	 * @throws NumberFormatException 
	 */
	public Page<AuthExpertAdvice> queryAuthExpertAdviceByPage(Map<String, String> map) throws SQLException{
		StringBuilder sql = new StringBuilder("SELECT DISTINCT TOP 1000 a.s_expert_id as expertId, a.s_group_id as groupId,b.s_expert_name as expertName, c.s_name as groupName, " +
				" a.s_create_time as createTime " +
				" from t_auth_expert_advice as a left join t_auth_expert as b on a.s_expert_id = b.p_id left join t_auth_group as c on a.s_group_id = c.p_id where 1=1 order by a.s_create_time desc");
		Page<AuthExpertAdvice> aea = dbHelper.getPage(sql.toString(),AuthExpertAdvice.class, Integer.valueOf(map.get("pageNumber")),
				Integer.valueOf(map.get("pageSize")), new Object[] {});
		return aea;
	}
}
